﻿using System;
using System.Collections.Generic;
using System.Text;
using DataTransferObject;
using System.Data;
namespace DataAccessObject
{
    public class BoDeDAO
    {
        private string _conn;
        public BoDeDAO(String conn)
        {
            this._conn = conn;
        }

        public clsBoDe CreateBoDeFromReader(IDataReader reader)
        {
            clsBoDe bd = new clsBoDe();
            bd.ChuyenMuc = new clsChuyenMuc();
            bd.MaDe = Convert.ToInt32(reader["MaBoDe"]);
            bd.TenDe = Convert.ToString(reader["TenBoDe"]);
            bd.ChuyenMuc.MaChuyenMuc = Convert.ToInt32(reader["MaChuyenMuc"]);
            bd.ChuyenMuc.TenChuyenMuc = Convert.ToString(reader["TenChuyenMuc"]);
            bd.SoCauHoi = Convert.ToInt32(reader["SoCauHoi"]);
            bd.ThoiGianLamBai = Convert.ToInt32(reader["ThoiGianLamBai"]);
            bd.MoTa = Convert.ToString(reader["MoTa"]);
            return bd;
        }
        // Thêm
        public int ThemBoDe(clsBoDe BoDe)
        {
            clsKetNoi _KetNoi = new clsKetNoi(_conn);
            return _KetNoi.ExcuteNonQuery(BoDe, "BoDe", "Insert");
        }
        // Sửa
        public void CapNhatBoDe(clsBoDe BoDe)
        {
            clsKetNoi _KetNoi = new clsKetNoi(_conn);
            _KetNoi.ExcuteNonQuery(BoDe, "BoDe", "Update");
        }
        // Xóa
        public int XoaBoDe(int MaBoDe)
        {
            clsKetNoi _KetNoi = new clsKetNoi(_conn);
            clsBoDe bd = new clsBoDe(MaBoDe);
            return _KetNoi.ExcuteNonQuery(bd, "BoDe", "Delete");
        }
        public void CapNhatSoCauHoi_ThoiGian(int socauhoi, int thoigian, int mabode)
        {
            clsKetNoi _KetNoi = new clsKetNoi(_conn);
            _KetNoi.CapNhatSoCauHoi_ThoiGian(socauhoi, thoigian, mabode);
        }
        // Lấy tất cả danh sách bộ đề
        public clsBoDe LayBoDeTheoId(int mabode)
        {
            clsKetNoi _KetNoi = new clsKetNoi(_conn);
            _KetNoi.OpenConnection();
            clsBoDe BoDe = new clsBoDe();
            IDataReader reader = _KetNoi.ExcuteReader("Select MaBoDe, TenBoDe, BoDe.MaChuyenMuc, TenChuyenMuc, SoCauHoi, ThoiGianLamBai, BoDe.MoTa, SoLuotThucHien from BoDe, ChuyenMuc where BoDe.MaChuyenMuc=ChuyenMuc.MaChuyenMuc and MaBoDe="+mabode+"  ORDER BY MaBoDe");
            if (reader == null)
            {
                throw new DataAccessException("Không tìm thấy Bộ đề yêu cầu.");
            }
            else
            {
                reader.Read();
                BoDe = new clsBoDe();
                BoDe = CreateBoDeFromReader(reader);
            }
            _KetNoi.CloseConnection();
            return BoDe;
        }
        // Lấy tất cả danh sách bộ đề
        public List<clsBoDe> LayTatCaDanhSachBoDe()
        {
            clsKetNoi _KetNoi = new clsKetNoi(_conn);
            _KetNoi.OpenConnection();
            List<clsBoDe> DanhSachBoDe = new List<clsBoDe>();
            IDataReader reader = _KetNoi.ExcuteReader("Select MaBoDe, TenBoDe, BoDe.MaChuyenMuc, TenChuyenMuc, SoCauHoi, ThoiGianLamBai, BoDe.MoTa, SoLuotThucHien from BoDe, ChuyenMuc where BoDe.MaChuyenMuc=ChuyenMuc.MaChuyenMuc ORDER BY MaBoDe");
            if (reader==null)
            {
                throw new DataAccessException("Hiện tại không có bộ đề nào trong cơ sở dữ liệu.");
            }
            else
            {
                while (reader.Read())
                {
                    clsBoDe BoDe = new clsBoDe();
                    BoDe = CreateBoDeFromReader(reader);
                    DanhSachBoDe.Add(BoDe);
                } 
            }
            _KetNoi.CloseConnection();
            return DanhSachBoDe;
        }
        // Lấy những câu hỏi không thuộc một bộ đề
        public List<clsCauHoi> DanhSachCauHoiKhongThuocBoDe(int MaBoDe)
        {
            ChiTietBoDeDAO chitietDAO = new ChiTietBoDeDAO(_conn);
            List<clsChiTietBoDe> DanhSachChiTiet = chitietDAO.DanhSachChiTietBoDeTheoMaBoDe(MaBoDe);
            string _DieuKien = "";
            for (int i = 0; i < DanhSachChiTiet.Count-1; i++)
            {
                _DieuKien += DanhSachChiTiet[i].MaCauHoi + ",";
            }
            if (DanhSachChiTiet.Count > 0)
            {
                _DieuKien = "and MaCauHoi not in(" + _DieuKien + DanhSachChiTiet[DanhSachChiTiet.Count - 1].MaCauHoi + ") ";
            }
            CauHoiDAO cauhoiDAO = new CauHoiDAO(_conn);
            List<clsCauHoi> DanhSachCauHoi = new List<clsCauHoi>();
            return cauhoiDAO.LayCauHoiTheoDieuKien(_DieuKien);
        }
        // Lấy những câu hỏi thuộc một bộ đề
        public List<clsCauHoi> DanhSachCauHoiThuocBoDe(int MaBoDe, int flag)
        {
            ChiTietBoDeDAO chitietDAO = new ChiTietBoDeDAO(_conn);
            List<clsChiTietBoDe> DanhSachChiTiet = chitietDAO.DanhSachChiTietBoDeTheoMaBoDe(MaBoDe);
            List<clsCauHoi> DanhSachCauHoi = new List<clsCauHoi>();
            if (DanhSachChiTiet.Count == 0)
                return DanhSachCauHoi;
            string _DieuKien = "";
            for (int i = 0; i < DanhSachChiTiet.Count - 1; i++)
            {
                _DieuKien += DanhSachChiTiet[i].MaCauHoi + ",";
            }
            if (DanhSachChiTiet.Count > 0)
            {
                _DieuKien = _DieuKien + DanhSachChiTiet[DanhSachChiTiet.Count - 1].MaCauHoi;
            }
            CauHoiDAO cauhoiDAO = new CauHoiDAO(_conn);
            if (flag == 0)
            {//Dùng cho trường hợp Xuất ra
                return cauhoiDAO.LayDanhSachCauHoiNgauNhienTheoChuoi(_DieuKien);
            }
            else
            {//Dùng cho GridView
                return cauhoiDAO.LayCauHoiTheoDieuKien("And MaCauHoi in ("+ _DieuKien + ") ");
            }
        }

        public int MaBoDe()
        {
            clsKetNoi _KetNoi = new clsKetNoi(_conn);
            int rs = Convert.ToInt32(_KetNoi.ExcuteScalar("Select IDENT_CURRENT('BoDe')"));
            return rs;
        }

        public int ThoiGianLamBai(int MaBoDe)
        {
            string sql = @"select sum(tg)
            From (Select MucDo.ThoiGianThucHien as tg From CauHoi,MucDo
            where MucDo.MaMucDo=CauHoi.MucDoKho and MaCauHoi
            in (select MaCauHoi From ChiTietBoDe where MaBoDe="+MaBoDe+")) as _CauHoi ";
            clsKetNoi _KetNoi = new clsKetNoi(_conn);
            int rs = Convert.ToInt32(_KetNoi.ExcuteScalar(sql));
            return rs;
        }
    }
}
